SQL Server: Execute SQL Agent job from T-SQL

Categories: Database; Tagged with: SQL • SQL Server • T-SQL; @ September 5th, 2012 19:02

Run SQL Agent job by this:

EXEC msdb.dbo.sp_start_job 'testJob';

Seems sp_start_job is asynchronous, here is a test:

-- Invoke SQL Agent job from T-SQL PRINT CONVERT(VARCHAR(24),GETDATE(),113) EXEC msdb.dbo.sp_start_job 'testJob'; PRINT CONVERT(VARCHAR(24),GETDATE(),113)

The results:
05 Sep 2012 18:55:11:747
Job ‘testJob’ started successfully.
05 Sep 2012 18:55:11:753

in testJob, we have one setp which is used for record the runtime:

PRINT CONVERT(VARCHAR(24),GETDATE(),113)
This job will output to one file, and in this file we can get the runtime date:

Job ‘testJob’ : Step 3, ‘recodTime’ : Began Executing 2012-09-05 18:55:12
05 Sep 2012 18:55:12:110 [SQLSTATE 01000]

MoreInfo:
1. EXECUTE (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188332.aspx
2. Different ways to execute a SQL Agent job: http://www.mssqltips.com/sqlservertip/1730/different-ways-to-execute-a-sql-agent-job/
3. sp_start_job Runs Asynchronously: http://www.daveturpin.com/2009/11/sp_start_job-runs-asynchronously/

SQLServer: Export SQL result into CSV file 导出SQL结果到CSV文件 <-> SSIS: Can not execute dataflow in Visual Studio